<?php
defined('BASEPATH') OR exit('No direct script access allowed');

//未提交信息的学生信息员
class NoFeedBack extends AdminController
{

    function __construct()
    {
        parent::__construct();
        $this->load->model("Student_feedback_model");
        $this -> load -> model("Admin_model");
        $this -> load ->library('ProjectService/admin_service');
//      $this -> sidebar = "Account-Admin";
    }

    public function Index($teacher_name=false)
    {
        $data=array();
        if($teacher_name){
            $data['teacher_name']=urldecode($teacher_name);
        }

            // 控制筛选项部门
        $deptlist=(new AdminDeptList)->getRemark()[0];
        for ($i=0; $i <count($deptlist) ; $i++) { 
            $deptlist[$i]='"'.$deptlist[$i].'"';
        }

        $connar['items']='distinct(dept_name)';
        $connar['where']=$this->sqlEscape("(role_id=?? or find_in_set(??,sub_role_id))",array(WebRole::Teacher[XPHEnum::Value],WebRole::Teacher[XPHEnum::Value]));
        $connar['where'].=" and dept_name in(".implode(',',$deptlist).")";
        $dept_name=$this->Admin_model->get_list_full($connar);

        $new_dept_name=array();
        foreach($dept_name as $k=>$v){
            if($v['dept_name']){
                $name = mb_substr($v['dept_name'],0,2,"utf-8");             
                if($name =='财经' ||$name =='商贸'||$name =='交通'||$name =='鉴湖'||$name =='黄酒'){
                    $new_dept_name[$k]['name'] = mb_substr($v['dept_name'],0,2,"utf-8");             
                }else if($name =='建筑' ||$name =='机电'||$name =='交通'){
                    $new_dept_name[$k]['name'] = mb_substr($v['dept_name'],0,4,"utf-8");   
                }else if($name =='设计'){
                    $new_dept_name[$k]['name'] = mb_substr($v['dept_name'],0,5,"utf-8");   
                }else{
                    $new_dept_name[$k]['name'] = $v['dept_name'];
                }
                 $new_dept_name[$k]['dept_name'] = $v['dept_name'];
            }           
        }

        $data['dept_name']= $new_dept_name;

        // $data['dept_name']= $dept_name;

           $sub_role=explode(',', getSess()['sub_role_id']);
         array_push($sub_role, getSess()['role_id']);

           if(in_array(WebRole::Manage[XPHEnum::Value], $sub_role)){
                $data['is_show']= 1;
           }else{
                $data['is_show']= 0;
           }

        $this->load->view('admin/nofeedbackList',$data);
    }


    function getDatas($params = array(), $isExport = false){
         $connar = array();
         //查询当前用户的角色
        $manage=$this->admin_service->getAdminById(getSess()['id']);

          $sub_role=explode(',', getSess()['sub_role_id']);
         array_push($sub_role, getSess()['role_id']);
             
         $where  = "db_student_feedback. del_flag=".CommonStatus::UnDeleted[XPHEnum::Value]." and db_admin.usercode != '' ";
         $where1 ='';

          if(isset($_POST['dept_name']) && $_POST['dept_name']!='-1'){
            // $where.=" and dept_name= '".$_POST['dept_name']."'";
             $where  .= " and db_student_feedback.dept_name like '%".$_POST['dept_name']."%'";
             $where1  .= " and db_admin.dept_name like '%".$_POST['dept_name']."%'";
        }
        
        if(isset($_POST['start_time']) && $_POST['start_time']!='' ){
            $where.=' and db_student_feedback.created>='.strtotime($_POST['start_time']."00:00:00");
        }
        if(isset($_POST['end_time'])  && $_POST['end_time']!=''){
            $where.=' and db_student_feedback.created<='.strtotime($_POST['end_time'].'23:59:59');
        }
        if(isset($_POST['username']) && $_POST['username']!=''){
            $where1 .=$this->sqlEscape(" and db_admin.username=??" ,array($_POST['username']));
        }


         //管理员可查询全校
        if(in_array(WebRole::Manage[XPHEnum::Value], $sub_role)){
            $where .= ' ';
        }else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $sub_role)){            
        //督导组长可查询本分院
           $where .=" and db_student_feedback.dept_name = '".$manage['dept_name']."'";
           $where1 .=" and db_admin.dept_name = '".$manage['dept_name']."'";
        }else{
            // 其余人查自己
            $where .=" and id=".getSess()['id'];
        }

        if (!$isExport) {
            $params = $_POST;
            $connar = $this->Student_feedback_model->pickPages($connar, $params);
             $pageSize=$params['pageSize'];
             $pageNo=$params['pageNo'];

            $sql ="SELECT db_admin.id, db_admin.usercode,db_admin.username, db_admin.dept_name, db_admin.role_id, db_admin.sub_role_id,db_admin.class_name  FROM  db_admin WHERE usercode NOT IN ( SELECT db_admin.usercode  FROM db_student_feedback LEFT JOIN db_admin ON db_admin.id = db_student_feedback.creator  WHERE  ".$where."  GROUP BY  db_admin.usercode  )  AND ( role_id = ".WebRole::Student[XPHEnum::Value]." OR role_id = ".WebRole::StudentInfoOfficer[XPHEnum::Value]." OR role_id = ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value]." OR FIND_IN_SET( ".WebRole::StudentInfoOfficer[XPHEnum::Value].", sub_role_id ) OR FIND_IN_SET( ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value].", sub_role_id ) ) AND sub_role_id != '' AND STATUS = '1' ".$where1." GROUP BY usercode  LIMIT ".$pageSize* ($pageNo-1) .", ".$pageSize;
           

            $sql1 = "select count(*) count from (SELECT db_admin.id, db_admin.usercode,db_admin.username, db_admin.dept_name, db_admin.role_id, db_admin.sub_role_id,db_admin.class_name  FROM  db_admin WHERE usercode NOT IN ( SELECT db_admin.usercode  FROM db_student_feedback
        LEFT JOIN db_admin ON db_admin.id = db_student_feedback.creator  WHERE  ".$where."  GROUP BY  db_admin.usercode  )  AND ( role_id = ".WebRole::Student[XPHEnum::Value]." OR role_id = ".WebRole::StudentInfoOfficer[XPHEnum::Value]." OR role_id = ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value]." OR FIND_IN_SET( ".WebRole::StudentInfoOfficer[XPHEnum::Value].", sub_role_id ) OR FIND_IN_SET( ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value].", sub_role_id ) ) AND sub_role_id != '' AND STATUS = '1' ".$where1." GROUP BY usercode ) no";
          
        }else{
             $sql ="SELECT db_admin.id, db_admin.usercode,db_admin.username, db_admin.dept_name, db_admin.role_id, db_admin.sub_role_id,db_admin.class_name  FROM  db_admin WHERE usercode NOT IN ( SELECT db_admin.usercode  FROM db_student_feedback LEFT JOIN db_admin ON db_admin.id = db_student_feedback.creator  WHERE  ".$where."  GROUP BY  db_admin.usercode  )  AND ( role_id = ".WebRole::Student[XPHEnum::Value]." OR role_id = ".WebRole::StudentInfoOfficer[XPHEnum::Value]." OR role_id = ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value]." OR FIND_IN_SET( ".WebRole::StudentInfoOfficer[XPHEnum::Value].", sub_role_id ) OR FIND_IN_SET( ".WebRole::UnitStudentInfoOfficer[XPHEnum::Value].", sub_role_id ) ) AND sub_role_id != '' AND STATUS = '1' ".$where1." GROUP BY usercode ";

        }

        $data["ret"] = $this->Admin_model->query($sql);

        if (!$isExport) {
            $count=$this->Admin_model->query($sql1);
            $data['count']=$count[0]['count'];
            $data['pageNo']=$pageNo;
            $data['totalPage']=ceil($data["count"] / $pageSize);
            echo json_encode($data);
        } else {
           $this-> load ->library('PHPExcel/PHPExcel');
            $excel = new PHPExcel();
            //公共样式
            $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

            $x_line=0;
            $y_line=1;

            //正式开始
            $excel = new PHPExcel();
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);

              //标题
            $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(0).'1:'.PHPExcel_Cell::stringFromColumnIndex(2).'1');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).'1','未提交信息的学生');
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $i=2;
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,'姓名');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,'学号');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,'部门');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,'班级');
              
        $i++;
        foreach ($data['ret'] as $k => $v) {
           
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,$v['username']);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,$v['usercode']);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,$v['dept_name']);          
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,$v['class_name']);          
            $i++;
        }
        //设置对齐
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(9).($i-1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(0).($i-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            $write = new PHPExcel_Writer_Excel2007($excel); 
            $director = "upload/export/" . date('Ymd', time()) . "/";
            if (!file_exists($director)) {
                //检查是否有该文件夹，如果没有就创建，并给予最高权限
                mkdir($director, 0777);
            }        
            $file_name ='学生未提交汇总'.date('Ymd').'.xlsx';           
            $write->save($director.iconv('utf-8', 'gb2312', $file_name));
            exit(retJson('返回地址',true,$director.$file_name));
        }

    }


}